﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class TestCaseBS : BusinessBase
    {
        public DataTable LoadTestCaseFromDB(Guid pProjectID, Guid pUseCaseID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           TestCaseID
                                       , MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , UseCaseID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID
                            ),
                            
                            TEST_CASES AS 
                            (
                             SELECT
	                            E.[TestCaseID]
                                , E.Description
                                , E.PreCondition
                                , E.PostCondition
                                , E.DataRequired
                                , E.SiraNo
                                , E.Priority
                                , (CASE WHEN M.[Order] IS NULL AND EX.OrderSign IS NOT NULL THEN '*' ELSE M.[Order] END) AS MssSiraNo
                                , EX.OrderSign
                                , EX.Name 
                                , ES.SiraNo AS StepNo
                                , PE.MssID
                                , PE.ExtentionID
                                , PE.ExtentionStepID
                                , UC.Name AS UseCaseName
                              FROM [SemiCaseDB].[dbo].[TEST_CASE] AS E
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON E.TestCaseID = PE.TestCaseID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[MSS] AS M ON M.MssID = PE.MssID 
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION] AS EX ON EX.ExtentionID = PE.ExtentionID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION_STEP] AS ES ON ES.StepID = PE.ExtentionStepID
                                    INNER JOIN [SemiCaseDB].[dbo].[USE_CASE] AS UC ON PE.UseCaseID = UC.UseCaseID
                              )
                                
                              SELECT 
                                *
                                , (CASE WHEN ExtentionID IS NOT NULL THEN UseCaseName + ' - ' + MssSiraNo + '.' + OrderSign + ' ' + Name 
                                        WHEN MssID IS NOT NULL THEN UseCaseName + ' - Mss'
                                        ELSE UseCaseName
                                   END) AS Relation
                              FROM TEST_CASES
                              ORDER BY UseCaseName, [MssSiraNo], OrderSign, SiraNo";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable LoadProjectTestCaseFromDB(Guid pProjectID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           TestCaseID
                                       , MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , UseCaseID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID
                            )

                             SELECT
	                            E.[TestCaseID]
                                , E.Description
                                , E.PreCondition
                                , E.PostCondition
                                , E.DataRequired
                                , E.SiraNo
                                , E.Priority
                                , (CASE WHEN M.[Order] IS NULL AND EX.OrderSign IS NOT NULL THEN '*' ELSE M.[Order] END) AS MssSiraNo
                                , EX.OrderSign
                                , EX.Name AS ExtensionName
                                , ES.SiraNo AS StepNo
                                , PE.MssID
                                , PE.ExtentionID
                                , PE.ExtentionStepID
                                , UC.Name AS UseCaseName
                              FROM [SemiCaseDB].[dbo].[TEST_CASE] AS E
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON E.TestCaseID = PE.TestCaseID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[MSS] AS M ON M.MssID = PE.MssID 
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION] AS EX ON EX.ExtentionID = PE.ExtentionID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[EXTENTION_STEP] AS ES ON ES.StepID = PE.ExtentionStepID
                                    INNER JOIN [SemiCaseDB].[dbo].[USE_CASE] AS UC ON PE.UseCaseID = UC.UseCaseID
                              ORDER BY UC.Name, M.[Order], EX.OrderSign, ES.SiraNo";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable GetTestCaseInfo(Guid pTestCaseID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           MssID
                                       , ExtentionID
                                       , ExtentionStepID
                                       , TestCaseID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE TestCaseID = @TestCaseID
                            )

                            SELECT
	                            E.[TestCaseID]
                                , E.Description
                                , E.PreCondition
                                , E.PostCondition
                                , E.DataRequired
                                , E.SiraNo
                                , E.Priority
                                , PE.MssID
                                , PE.ExtentionID
                                , PE.ExtentionStepID
                              FROM [SemiCaseDB].[dbo].[TEST_CASE] AS E
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON E.TestCaseID = PE.TestCaseID
                              WHERE E.TestCaseID = @TestCaseID";

            data.AddSqlParameter("TestCaseID", pTestCaseID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void CreateTestCase(Guid pProjectID,
                                   Guid pUseCaseID,
                                   string pDescription,
                                   string pPrecondition,
                                    string pPostCondition,
                                    string pDataRequired,
                                    string pSiraNo,
                                    Guid? pMssID,
                                    Guid? pExtentionID,
                                    Guid? pExtentionStepID,
                                    string pPriority)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[TEST_CASE]
                                       ([TestCaseID]
                                        ,[Description]
                                        ,[PreCondition]
                                        ,[PostCondition]
                                        ,[SiraNo]
                                        ,[DataRequired]
                                        , Priority)
                                 VALUES
                                       (@TestCaseID
                                       ,@Description
                                       ,@PreCondition
                                       ,@PostCondition
                                       ,@SiraNo
                                       ,@DataRequired
                                       ,@Priority);

                                INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID]
                                       ,[ExtentionID]
                                       ,TestCaseID
                                       ,ExtentionStepID)
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID
                                       ,@ExtentionID
                                       ,@TestCaseID
                                       ,@TestCaseStepID)";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("TestCaseID", Guid.NewGuid(), SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("MssID", pMssID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("ExtentionID", pExtentionID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("TestCaseStepID", pExtentionStepID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Description", pDescription, SqlDbType.VarChar);
            data.AddSqlParameter("PreCondition", pPrecondition, SqlDbType.VarChar);
            data.AddSqlParameter("PostCondition", pPostCondition, SqlDbType.VarChar);
            data.AddSqlParameter("DataRequired", pDataRequired, SqlDbType.VarChar);
            data.AddSqlParameter("SiraNo", pSiraNo, SqlDbType.Int);
            data.AddSqlParameter("Priority", pPriority, SqlDbType.VarChar);

            data.ExecuteStatement(sqlText);
        }

        public void UpdateTestCase(Guid pProjectID,
                                   Guid pUseCaseID,
                                    string pDescription,
                                        string pPrecondition,
                                    string pPostCondition,
                                    string pDataRequired,
                                    Guid pTestCaseID,
                                    Guid? pMssID,
                                    Guid? pExtentionID,
                                    Guid? pExtentionStepID,
                                    string pPriority)
        {

            IData data = GetDataObject();
            string sqlText = @" DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                               WHERE TestCaseID = @TestCaseID

                            INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID]
                                       ,[ExtentionID]
                                       ,TestCaseID
                                       ,ExtentionStepID)
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID
                                       ,@ExtentionID
                                       ,@TestCaseID
                                       ,@TestCaseStepID)

                             UPDATE [SemiCaseDB].[dbo].[TEST_CASE]
                               SET [Description] = @Description
                                  ,[PreCondition] = @PreCondition
                                  ,[PostCondition] = @PostCondition
                                  ,[DataRequired] = @DataRequired
                                  ,Priority = @Priority
                             WHERE TestCaseID = @TestCaseID";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("TestCaseID", pTestCaseID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("MssID", pMssID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("ExtentionID", pExtentionID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("TestCaseStepID", pExtentionStepID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Description", pDescription, SqlDbType.VarChar);
            data.AddSqlParameter("PreCondition", pPrecondition, SqlDbType.VarChar);
            data.AddSqlParameter("PostCondition", pPostCondition, SqlDbType.VarChar);
            data.AddSqlParameter("DataRequired", pDataRequired, SqlDbType.VarChar);
            data.AddSqlParameter("Priority", pPriority, SqlDbType.VarChar);

            data.ExecuteStatement(sqlText);
        }

        public void DeleteTestCase(Guid pTestCaseID)
        {
            IData data = GetDataObject();
            string sqlText = @"BEGIN TRAN;

                               DELETE FROM SemiCaseDB.dbo.TEST_CASE
                               WHERE TestCaseID = @TestCaseID


                               DELETE FROM [SemiCaseDB].[dbo].[RELATION]
                               WHERE TestCaseID = @TestCaseID

                               COMMIT TRAN;";

            data.AddSqlParameter("TestCaseID", pTestCaseID, SqlDbType.UniqueIdentifier);
            data.ExecuteStatement(sqlText);
        }
    }
}
